package com.attendance.www.hr.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.commons.lang.time.DateUtils;

import com.attendance.www.hr.beans.AttendanceBean;
import com.attendance.www.hr.beans.EmployeeBean;
import com.attendance.www.hr.entitys.CheckinoutMorning;

public class AccessUtils {
	// 获取连接
	public static String url = "jdbc:ucanaccess://";
	// 读取地址失败的key
	public static String failedKey = "FK";

	// 获取access数据库数据
	public static Map<String, AttendanceBean> getAccessInfo(String urlPart, Date startDate,
			List<EmployeeBean> employeeBeanYesterdayList) {

		// 保存考勤map集合
		Map<String, AttendanceBean> attMap = new HashMap<>();

		try {
			Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		}

		String[] strs = urlPart.split(",");
		String address = null;
		String errorMessage = "";
		for (int i = 0, len = strs.length; i < len; i++) {
			Connection conn = null;
			Boolean hasConn = true;
			try {
				conn = DriverManager.getConnection(url + strs[i].toString());
			} catch (Exception ex) {
				hasConn = false;// 地址失效时
				ex.printStackTrace();
				errorMessage = errorMessage + ex.getMessage() + ";";
			}
			// 建立连接后，查询集合
			if (hasConn) {
				Statement stat = null;
				ResultSet rs = null;
				attMap = getResultSetAttendanceMap(attMap, rs, strs[i].toString(), conn, stat, startDate,
						employeeBeanYesterdayList);
			} else {// 地址失效时
				if (address != null) {
					address = address + strs[i].toString() + ";";
				} else {
					address = strs[i].toString() + ";";
				}
			}
		}
		if (address != null) {
			AttendanceBean att = new AttendanceBean();
			att.setAddress(address);
			att.setErrorMessage(errorMessage);
			attMap.put(failedKey, att);
		}
		return attMap;
	}

	// 处理ResultSet的方法
	private static Map<String, AttendanceBean> getResultSetAttendanceMap(Map<String, AttendanceBean> attMap,
			ResultSet rs, String urlPart, Connection conn, Statement stat, Date startDate,
			List<EmployeeBean> employeeBeanYesterdayList) {
		try {
			// 考勤时间，昨天6点
			Calendar calStartDate = Calendar.getInstance();
			/* calStartDate.setTime(DateUtils.addDays(new Date(), -1)); */
			calStartDate.setTime(startDate);
			calStartDate.set(Calendar.HOUR_OF_DAY, 6);
			calStartDate.set(Calendar.MINUTE, 0);
			calStartDate.set(Calendar.SECOND, 0);
			calStartDate.set(Calendar.MILLISECOND, 0);
			startDate = calStartDate.getTime();
			Date endDate = DateUtils.addDays(startDate, 1);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String startDateStr = "#" + sdf.format(startDate) + "#";
			String endDateStr = "#" + sdf.format(endDate) + "#";

			String sql = "Select ch.userid, ch.checktime, ufo.badgenumber From checkinout ch "
					+ "left join userinfo ufo on ufo.userid = ch.userid " + "where ch.checktime > " + startDateStr
					+ "and ch.checktime <= " + endDateStr + "order by ch.userid ,ch.checktime asc";
			// +"and ufo.badgenumber not like '50___' "
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			attMap = getAttendanceMap(attMap, rs, startDate, urlPart, employeeBeanYesterdayList);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 关闭资源
		try {
			if (rs != null) {
				rs.close();
			}
			if (stat != null) {
				stat.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return attMap;
	}

	// 考勤数据循环方法
	private static Map<String, AttendanceBean> getAttendanceMap(Map<String, AttendanceBean> attMap, ResultSet rs,
			Date startDate, String urlPart, List<EmployeeBean> employeeBeanYesterdayList) {
		AttendanceBean attendanceBean = new AttendanceBean();
		try {
			String nightTime = "06:00";
			while (rs.next()) {
				String attTime = rs.getTime("CHECKTIME").toString().substring(0, 5);
				String employeeNo = rs.getString("badgenumber");
				if (employeeNo.length() == 4) {// 员工号在打卡机里是4位的，补0处理
					employeeNo = "0" + employeeNo;
				}
				String employeeNoStr = employeeNo;
				// 过滤员工，根据日期降序
				List<EmployeeBean> ebList = employeeBeanYesterdayList.stream()
						.filter(s -> s.getEmployeeNo().equals(employeeNoStr))
						.sorted(Comparator.comparing(EmployeeBean::getFromDate).reversed())
						.collect(Collectors.toList());
				
				if (attMap.get(employeeNo) == null) {
					attendanceBean = new AttendanceBean();
					attendanceBean.setAttendanceDate(startDate);
					attendanceBean.setEmployeeNo(employeeNo);
					attendanceBean.setAddress(urlPart);
					attendanceBean.setStartTime(attTime);
					attendanceBean.setEndTime(attTime);
					attMap.put(employeeNo, attendanceBean);
				} else {// 同一个的考勤
					attendanceBean = attMap.get(employeeNo);
					// 和早晚时间比较
					String startTime = attendanceBean.getStartTime();
					String endTime = attendanceBean.getEndTime();

					boolean type = true;
					// 判断员工班次考勤时间是否有跨天存在
					if (employeeBeanYesterdayList.size() > 0) {				
						// 判断是否为跨天考勤时间
						if (ebList.size() > 0) {
							EmployeeBean employeeBean = ebList.get(0);
							if (TimeUtils.compareTime(employeeBean.getStartFromTime(),
									employeeBean.getEndFromTime()) == 1) {
								attendanceBean.setStartTime(attTime);
								attendanceBean.setEndTime(attTime);
								type=false;
							}
							attMap.put(employeeNo, attendanceBean);
						}
					}
				
					// 计算正常考勤时间
					if (type) {
						if (TimeUtils.compareTime(attTime, nightTime) < 0) {// 凌晨打卡处理
							attendanceBean.setEndTime(attTime);
						} else {
							if (TimeUtils.compareTime(attTime, endTime) > 0) {// 晚上打卡时间
								attendanceBean.setEndTime(attTime);
							} else if (TimeUtils.compareTime(attTime, startTime) < 0) {// 早上打卡时间
								attendanceBean.setStartTime(attTime);
							}
						}
						attMap.put(employeeNo, attendanceBean);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return attMap;
	}

	/**
	 * 获取access数据库中早上0点-8点30打卡信息
	 * 
	 * @param urlPart
	 *            考勤打卡数据地址
	 * @return
	 */
	public static List<CheckinoutMorning> getAccessInfoMorning(String urlPart, Date startDate) {
		// 保存考勤List集合
		List<CheckinoutMorning> cmList = new ArrayList<>();
		try {
			Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("加载驱动失败");
		}
		String[] strs = urlPart.split(",");
		String errorMessage = "";
		for (int i = 0, len = strs.length; i < len; i++) {
			Connection conn = null;
			Boolean hasConn = true;
			try {
				conn = DriverManager.getConnection(url + strs[i].toString());
			} catch (Exception ex) {
				hasConn = false;// 地址失效时
				ex.printStackTrace();
				errorMessage = errorMessage + ex.getMessage() + ";";
			}
			// 建立连接后，查询集合
			if (hasConn) {
				Statement stat = null;
				ResultSet rs = null;
				// TODO
				try {
					// 打卡时间，昨天0点-8点30
					Calendar calStartDate = Calendar.getInstance();
					/*
					 * calStartDate.setTime(DateUtils.addDays(new Date(), -1));
					 * calStartDate.set(Calendar.HOUR_OF_DAY,0);
					 * calStartDate.set(Calendar.MINUTE,0); calStartDate.set(Calendar.SECOND,0);
					 * calStartDate.set(Calendar.MILLISECOND,0);
					 */
					// Date startDate=calStartDate.getTime();
					calStartDate.setTime(startDate);
					calStartDate.set(Calendar.HOUR_OF_DAY, 8);
					calStartDate.set(Calendar.MINUTE, 30);
					Date endDate = calStartDate.getTime();

					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					String startDateStr = "#" + sdf.format(startDate) + "#";
					String endDateStr = "#" + sdf.format(endDate) + "#";

					String sql = "Select ch.userid, ch.checktime, ufo.badgenumber From checkinout ch "
							+ "left join userinfo ufo on ufo.userid = ch.userid " + "where ch.checktime > "
							+ startDateStr + "and ch.checktime < " + endDateStr
							+ "order by ch.userid ,ch.checktime asc";
					stat = conn.createStatement();
					rs = stat.executeQuery(sql);
					// TODO 循环
					CheckinoutMorning checkinoutMorning = new CheckinoutMorning();
					try {
						while (rs.next()) {
							String attTime = rs.getTime("CHECKTIME").toString().substring(0, 5);
							String employeeNo = rs.getString("badgenumber");
							if (employeeNo.length() == 4) {// 员工号在打卡机里是4位的，补0处理
								employeeNo = "0" + employeeNo;
							}
							checkinoutMorning = new CheckinoutMorning();
							checkinoutMorning.setAttendanceDate(startDate);
							checkinoutMorning.setEmployeeNo(employeeNo);
							checkinoutMorning.setMorningTime(attTime);
							cmList.add(checkinoutMorning);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// 关闭资源
				try {
					if (rs != null) {
						rs.close();
					}
					if (stat != null) {
						stat.close();
					}
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} else {// 地址失效时
			}
		}
		return cmList;
	}

}
